/**
 * (c) 2018 Rin Audit Plugin v0.0.2 | No UI
 * https://gitee.com/immane/js-audit
 *
 * @author: Rin Peng <immane.wc@gmail.com>
 * @description: Create financial statements from recording voucher.
 * 
 * ROADMAP:
 * Trial Balances
 */

// initilize.
const DEBUG = true;

// TEST: 2015-2017
/*
const AUDIT_SLUG_COLUMN = 2;
const AUDIT_DATE_COLUMN = 0;
const AUDIT_DEBIT_COLUMN = 9;
const AUDIT_CREDIT_COLUMN = 10;
const AUDIT_CURRENCY_COLUMN = 7;
const AUDIT_COMMENT_COLUMN = 4;
const AUDIT_ACCOUNT_COLUMN = 6;
const AUDIT_COSTING_NAME_COLUMN = 12;
const AUDIT_ACCOUNT_CODE_COLUMN = 5;
*/

// Column distribution
const BALANCE_CODE_COLUMN = 0;
const BALANCE_NAME_COLUMN = 1;
const BALANCE_INITIAL_DEBIT_COLUMN = 3;
const BALANCE_INITIAL_CREDIT_COLUMN = 4;
const BALANCE_CLOSING_DEBIT_COLUMN = 9;
const BALANCE_CLOSING_CREDIT_COLUMN = 10;

// yonyou
const AUDIT_SLUG_COLUMN = 5;
const AUDIT_DATE_COLUMN = 3;
const AUDIT_DEBIT_COLUMN = 11;
const AUDIT_CREDIT_COLUMN = 12;
const AUDIT_CURRENCY_COLUMN = 9;
const AUDIT_COMMENT_COLUMN = 6;
const AUDIT_ACCOUNT_COLUMN = 8;
const AUDIT_COSTING_NAME_COLUMN = 5;
const AUDIT_ACCOUNT_CODE_COLUMN = 7;


class Audit {

	// initialize
	constructor(options) {
		
        console.log('Audit system is initializing.');

        if(typeof options === "undefined") {
            throw "Unknown audit options.";
        }
        else if(typeof options.voucher_store_name === "undefined") {
            throw "Unknown audit options[voucher_store_name].";
        }
        else if(typeof options.initial_balance_name === "undefined") {
            throw "Unknown audit options[initial_balance_name].";
        }
        else if(typeof options.from_date === "undefined") {
            throw "Unknown audit options[from_date].";
        }
        else if(typeof options.to_date === "undefined") {
            throw "Unknown audit options[to_date].";
        }


		// xls source url
		//const xls_url = './data/1-会计分录序时薄2015-2017.xls';
		this.voucher_store_name = options.voucher_store_name; //'2011年.xls';
		this.initial_balance_name = options.initial_balance_name; //'2011年科目余额表.xls';
		this.xls_base_url = './data/';
		this.voucher_store_url = this.xls_base_url + this.voucher_store_name;
		this.initial_balance_url = this.xls_base_url + this.initial_balance_name;

		this.xls_serial = CryptoJS.MD5(this.voucher_store_name).toString();
		this.voucher_table = "voucher_store";
		this.account_table = "user_account";


		// db source
		this.db = null;
		this.db_name = typeof options.db_name === "undefined" ? "audit_offline" : options.db_name;
		this.db_name += "_" + this.xls_serial;
		this.db_version = typeof options.db_version === "undefined" ? 1 : options.db_version;

		this.write_db = false;

		this.from_date = options.from_date; // new Date('2011-01-01');
		this.to_date = options.to_date;     // new Date('2012-01-01');
	}


	// generate trial balance
	generate_trial_balance() { 

		var self = this;
		return new Promise( (resolve, reject) => {

			console.log('Generating trial balance.');
			
			// expend account
			this.expend_account_flat().then( function(r) {
				var sum = new Array;
				var p = new Array;
				var results = new Array();
				var accounts = r.accounts;

				if(DEBUG) console.log('All accounts', accounts)

				for( var i in accounts ) {
					
					p[i] = self.expend_account_flat(accounts[i]).then(
						async function(expended_accounts) {
							
							// expend each accounts to flat.
							var accountCode = expended_accounts['code'];
							var expended_accounts = await self.expend_account_flat(accountCode);

							var inner_accounts = expended_accounts['accounts'];
							inner_accounts.push(accountCode);
							
							if(DEBUG) console.log('expend_account_flat', accountCode, '=>', inner_accounts);

							var data = new Array();
							sum[accountCode] = new Array;
							sum[accountCode]['debit']   = 0;
							sum[accountCode]['credit']  = 0;
							sum[accountCode]['balance'] = 0;

							for( var j in inner_accounts ) {
								// sum expended accounts
								let res = await self.sum_account(inner_accounts[j], self.from_date, self.to_date);

								// show in grids.
								data[res.code] = ([res.code, res.name, res.balance.toFixed(2)]);
								if(DEBUG) console.log( 'balance', new Date(), res );
								
								sum[accountCode]['debit'] += res.debit;
								sum[accountCode]['credit'] += res.credit;
								sum[accountCode]['balance'] += res.balance;
								$('#processing_account').text(res.code);
							}

							var trial_balance = await self.find_by_val(self.account_table, "code", accountCode);
							var total_balance = trial_balance['initial_debit'] - trial_balance['initial_credit'] + sum[accountCode]['balance'];
							var final_debit = total_balance > 0 ? total_balance : 0;
							var final_credit = total_balance < 0 ? -total_balance : 0;

							results.push([
								accountCode,
								await self.get_account_name(accountCode),
								trial_balance['initial_debit'],
								trial_balance['initial_credit'],
								sum[accountCode]['debit'],
								sum[accountCode]['credit'],
								final_debit,
								final_credit,
							]);
						}
					);
				}

				Promise.all(p).then( function() {
					console.log('Load all accounts success.');
					results = results.sort( function(x, y) { return x[0] < y[0] ? -1 : 1 } );

					// process results
					resolve(results)
				});
			});	
		});	
	}

	
	// get voucher from one account
	get_voucher(accountCode, fromDate, toDate) {
		let self = this;
		return new Promise( (resolve, reject) => {
			var transaction = self.db.transaction([self.voucher_table, self.account_table], "readonly");
			var store = transaction.objectStore(self.voucher_table);
			
			var index = store.index("account_date");
			var vouchers = new Array;

			if(fromDate != "" && toDate != "") {
				var range = IDBKeyRange.bound([accountCode, fromDate], [accountCode, toDate]);
			} else if(fromDate == "") {
				var range = IDBKeyRange.upperBound([accountCode, fromDate]);
			} else {
				var range = IDBKeyRange.lowerBound([accountCode, toDate]);
			}

			index.openCursor(range).onsuccess = function (e) {  
				var cursor = e.target.result;
				
				if(cursor) {
					vouchers.push(cursor.value);
					cursor.continue();
				}
				else {
					if(DEBUG) console.log('get_voucher', vouchers);
					resolve(vouchers);
				}
			}		
		})		
	}

	// get voucher by slug
	async get_voucher_by_slug_date(slug, date) {
		let self = this;
		return new Promise( (resolve, reject) => {
			var transaction = self.db.transaction([self.voucher_table, self.account_table], "readonly");
			var store = transaction.objectStore(self.voucher_table);
			
			var index = store.index("slug_date");
			var range = IDBKeyRange.only([slug, date]);
			var vouchers = new Array;

			index.openCursor(range).onsuccess = function (e) {  
				var cursor = e.target.result;
				
				if(cursor) {
					vouchers.push(cursor.value);
					cursor.continue();
				}
				else {
					if(DEBUG) console.log('get_voucher_by_slug_date', vouchers);
					resolve(vouchers);
				}
			}		
		})		
	}

	// get all voucher from recursed accounts
	async get_voucher_from_recurse_accounts(accountCode, fromDate, toDate) {
		var self = this;
		var res = await this.expend_account_flat(accountCode);
		var accounts = res.accounts;
		var vouchers = new Array;
 
		accounts.push( accountCode );

		for(var i in accounts) {
			vouchers = vouchers.concat(
				await self.get_voucher(accounts[i], fromDate, toDate)
			);
		}
		if(DEBUG) console.log('get_voucher_from_recurse_accounts', vouchers);

		return vouchers;
	}

	// get account while depth is 0
	get_main_accounts() {
		let self = this;
		return new Promise( (resolve, reject) => {
			var transaction = self.db.transaction([self.account_table], "readonly");
			var store = transaction.objectStore(self.account_table);
			var index = store.index("depth");
			var range = IDBKeyRange.only(0);
			var accounts = new Array;

			index.openCursor(range).onsuccess = function(e) {
				var cursor = e.target.result; 	
				if(cursor) {
					if(DEBUG) console.log('get_main_account', cursor.value['code']);
					accounts.push(cursor.value['code']);
					cursor.continue();
				}
				else {
					resolve(accounts);
				}
			}
		});
	}

	// get all accounts
	get_all_accounts() {
		let self = this;
		return new Promise( (resolve, reject) => {
			var transaction = self.db.transaction([self.account_table], "readonly");
			var store = transaction.objectStore(self.account_table);
			var accounts = new Array;

			store.getAll().onsuccess = function(e) {
				var accounts = e.target.result; 	
				if(DEBUG) console.log('get_all_accounts', accounts);
				resolve(accounts);
			}
		});
	}

	async get_account_children(parent_code) {
		let self = this;
		return new Promise( (resolve, reject) => {
			var transaction = self.db.transaction([self.account_table], "readonly");
			var store = transaction.objectStore(self.account_table);
			var accounts = new Array;
			var index = store.index("parent");
			var range = IDBKeyRange.only(parent_code);
			var accounts = new Array;

			index.openCursor(range).onsuccess = function(e) {
				var cursor = e.target.result; 	
				if(cursor) {
					accounts.push(cursor.value);
					cursor.continue();
				}
				else {
					if(DEBUG) console.log('get_account_children', accounts);
					resolve(accounts);
				}
			}

		});
	}

	// get value from database
	find_by_val(table, key, value) {
		return new Promise( (resolve, reject) => {
			if(!this.db) throw "Database is not found.";

			var transaction = this.db.transaction([table], "readonly");
			var store = transaction.objectStore(table);
			var index = store.index(key);

			index.get(value).onsuccess = function (e) {
				if(e.target.result) {
					let object = e.target.result;
					resolve(object);
				}
				else {
					reject(e);
				}
			}
		});
	}

	// get account name from database
	get_account_name(accountCode) {
		let self = this
		return new Promise( (resolve, reject) => {
			self.find_by_val(self.account_table, "code", accountCode)
				.then( (object) => {
					let name = object.name;
					resolve(name);
			});
		});
	}


	// expend account to an array
	expend_account_flat(accountCode = "", depth = 0) { 
		return new Promise( (resolve, reject) => {
			if(!this.db) throw "Database is not found.";

			var transaction = this.db.transaction([this.account_table], "readonly");
			var store = transaction.objectStore(this.account_table);

			store.getAll().onsuccess = function (e) {
				var recursion;
				resolve(
					(recursion = function(data, accountCode = "", depth = 0, accounts) {
						if( !depth ) accounts = new Array;
						
						for( var i in data ) {
							var account = data[i];
							if( account.parent === accountCode ) {
								if(DEBUG) console.log('recursion', depth, account);
								accounts.push( account.code );
								recursion(data, account.code, depth + 1, accounts) 
							}
						}
						return {
							'accounts': accounts,
							'code': accountCode
						}
					})(e.target.result, accountCode)
				);
			}		
		});
	}


	// sum account width account code
	sum_account(accountCode, fromDate, toDate) {
		return new Promise((resolve, reject) => {
			var transaction = this.db.transaction([this.voucher_table, this.account_table], "readonly");
			var store = transaction.objectStore(this.voucher_table);
            
			var index = store.index("account_date");
			
			if(fromDate != "" && toDate != "") {
				var range = IDBKeyRange.bound([accountCode, fromDate], [accountCode, toDate]);
			} else if(fromDate == "") {
				var range = IDBKeyRange.upperBound([accountCode, fromDate]);
			} else {
				var range = IDBKeyRange.lowerBound([accountCode, toDate]);
			}

            var times = 0;
			var debit = 0;
			var credit = 0;
			var accountName;

			index.openCursor(range).onsuccess = function(e) {
				var cursor = e.target.result;
				if(cursor) {
					// cursor loop.
                    times++;

                    // calculate.
                    debit  += cursor.value['debit'];
                    credit += cursor.value['credit'];
                    accountName = cursor.value['account'];

                    // next
					cursor.continue();
				}
				else {
                    if(DEBUG) console.log('run times', accountCode, times);

					resolve ( {
						code: accountCode,
						name: accountName ? accountName: '',
                        debit: debit,
                        credit: credit,
						balance: debit - credit,
					});
				}
			}
		});
	}


	// delete database
	delete_db() {
		window.indexedDB.deleteDatabase(this.db_name);
	}


	// append data to database
	add_to_db(table, data) {
		var transaction = this.db.transaction([table], "readwrite");
		var objectStore = transaction.objectStore(table);

		for( var i in data) {
			objectStore.add(data[i]);        
		}		
	}


	// create database and load it
	load_db() {
		var self = this;
		return new Promise( function(resolve, reject) {
			var DBOpenRequest = window.indexedDB.open(self.db_name, self.db_version);
			
			DBOpenRequest.onerror = function(e) {
				if(DEBUG) console.log('db open fail');
				reject(e);
			};
			
			DBOpenRequest.onsuccess = function(event) {        
				console.log('Database open success');

				self.db = DBOpenRequest.result;
				resolve();
			};
			
			DBOpenRequest.onupgradeneeded = function(event) {
				self.db = event.target.result;
			 
				self.db.onerror = function(event) {
					if(DEBUG) console.log(event)
					console.log('Database open fail');
					reject(event);
				};
			
				var objectAuditStore = self.db.createObjectStore(self.voucher_table, { 
					keyPath: 'id',
					autoIncrement: true
				});
			
				objectAuditStore.createIndex('id', 'id', {unique: true});
				objectAuditStore.createIndex('slug', 'slug');
				objectAuditStore.createIndex('account', 'account');
				objectAuditStore.createIndex('account_code', 'account_code');
				objectAuditStore.createIndex('debit', 'debit');
				objectAuditStore.createIndex('credit', 'credit');
				objectAuditStore.createIndex('comment', 'comment');
				objectAuditStore.createIndex('date', 'date');
				objectAuditStore.createIndex('slug_date', ['slug', 'date']);
				objectAuditStore.createIndex('account_date', ['account_code', 'date'], {unique:false});

				var objectAccountStore = self.db.createObjectStore(self.account_table, { 
					keyPath: 'code',
				});
			
				objectAccountStore.createIndex('code', 'code', {unique: true});
				objectAccountStore.createIndex('name', 'name');
				objectAccountStore.createIndex('depth', 'depth');
				objectAccountStore.createIndex('parent', 'parent');
				objectAccountStore.createIndex('initial_debit', 'initial_debit');
				objectAccountStore.createIndex('initial_credit', 'initial_debit');
				objectAccountStore.createIndex('closing_debit', 'closing_debit');
				objectAccountStore.createIndex('closing_credit', 'closing_credit');

				self.write_db = true;

				console.log('Database create success');
			};
		});
	}

	// Get cells from workbook
	get_workbook_cell(worksheet, row, column, type) {
		var res = worksheet[XLSX.utils.encode_cell({ r: row, c: column })]
		if(typeof type !== 'undefined') {
			try {
				var res = res[type];
			}
			catch(e) {
				return null;
			}
		}
		return typeof res !== 'undefined' ? res : '';
	};


	// Translate account workbook to object and initialize accounts
	accounts_workbook_to_cell(workbook) {
		var self = this

		var sheet_name_list = workbook.SheetNames;
		var cells = [];
		
		sheet_name_list.forEach(function(sheet) { 
			/* iterate through sheets */
			var worksheet = workbook.Sheets[sheet];
			
			var range = XLSX.utils.decode_range(worksheet['!ref']);
			if(DEBUG) console.log('Accounts workbook length', range.e);

			for(var r = 1 /* skip column line */; r < range.e.r; r++) {
				var get_cell = (c, t) => {
					return self.get_workbook_cell(worksheet, r, c, t);
				}

				var code = String(get_cell(BALANCE_CODE_COLUMN, 'v'))
				var name = get_cell(BALANCE_NAME_COLUMN, 'v')
				var initial_debit = get_cell(BALANCE_INITIAL_DEBIT_COLUMN, 'v')
				var initial_credit = get_cell(BALANCE_INITIAL_CREDIT_COLUMN, 'v')
				var closing_debit = get_cell(BALANCE_CLOSING_DEBIT_COLUMN, 'v')
				var closing_credit = get_cell(BALANCE_CLOSING_CREDIT_COLUMN, 'v')

				cells.push( {
					'code'	: code,
					'name'	: name,
					'depth'	: code.split('.').length - 1,
					'parent': code.substring(0, code.lastIndexOf(['.'])),
					'initial_debit'	: initial_debit ? initial_debit : 0,
					'initial_credit': initial_credit ? initial_credit: 0,
					'closing_debit'	: closing_debit ? closing_debit : 0,
					'closing_credit': closing_credit ? closing_credit: 0,
				});
			}	
		});

		if(DEBUG) console.log('Workbook cells', cells);

		return cells;

	}


	// Translate voucher workbook to object
	voucher_workbook_to_cell(workbook) {
		var self = this;
		
		var sheet_name_list = workbook.SheetNames;
		var cells = [];
		var saved_date;
		var slug;
		
		sheet_name_list.forEach(function(sheet) { 
			/* iterate through sheets */
			var worksheet = workbook.Sheets[sheet];
			
			var range = XLSX.utils.decode_range(worksheet['!ref']);
			if(DEBUG) console.log('Audit workbook length', range.e);

			for(var r = 1 /* skip column line */; r < range.e.r; r++) {
				var get_cell = (c, t) => {
					return self.get_workbook_cell(worksheet, r, c, t);
				}

				// append data cell to documents.
				if(get_cell(AUDIT_DATE_COLUMN, 'w') != null)
					saved_date = get_cell(AUDIT_DATE_COLUMN, 'w');
				var common_cell = get_cell(AUDIT_COMMENT_COLUMN, 'v');
				var account = get_cell(AUDIT_ACCOUNT_COLUMN, 'v');
				var acccode = get_cell(AUDIT_ACCOUNT_CODE_COLUMN, 'v');
				if( acccode == null ) {
					console.log('voucher_workbook_to_cell fail in line ' + r + '.');
					continue;
				}
				if(get_cell(AUDIT_SLUG_COLUMN, 'w') != null)
					slug = get_cell(AUDIT_SLUG_COLUMN, 'w');
				var credit = get_cell(AUDIT_CREDIT_COLUMN, 'v')
				var debit = get_cell(AUDIT_DEBIT_COLUMN, 'v')

				cells.push( {
					'date'		: new Date(saved_date), // Not a standard initialize method.
					'slug'		: slug,
					'credit'	: credit ? credit : 0,
					'debit'		: debit ? debit : 0,
					'account'	: account,
					'account_code': acccode,
					'comment'	: common_cell,
				});
			}	
		});

		return cells;
	}


	// Download data from urls
	download_xls(url) {
		return new Promise(
			(resolve, reject) => {
			
				// load data from web
				var oReq;
				if(window.XMLHttpRequest) oReq = new XMLHttpRequest();
				else if(window.ActiveXObject) oReq = new ActiveXObject('MSXML2.XMLHTTP.3.0');
				else throw "XHR unavailable for your browser";

				oReq.open("GET", url, true);

				if(typeof Uint8Array !== 'undefined') {
					oReq.responseType = "arraybuffer";
					oReq.onload = function(e) {
						console.log("Request onload", new Date());
						var arraybuffer = oReq.response;
						var data = new Uint8Array(arraybuffer);

						var workbook = XLSX.read(data, {type:"array"});
						resolve( workbook );
					};
				} else {
					oReq.setRequestHeader("Accept-Charset", "x-user-defined");	
					oReq.onreadystatechange = function() { 
						if(oReq.readyState == 4 && oReq.status == 200) {
							var ff = convertResponseBodyToText(oReq.responseBody);
							if(typeof console !== 'undefined') 
								console.log("Request onload", new Date());

							var workbook = XLSX.read(ff, {type:"binary"});
							resolve( workbook );
						} 
					};
				}

				oReq.send();
			}
		);
	}


	// Audit system ready
    ready(then) { 
        var self = this;
        
        // load db
        self.load_db().then( function() {

            console.log("Database loaded in", new Date());
            console.log('Downloading xls...');

            self.download_xls(self.initial_balance_url).then( (workbook) => {
                var user_accounts = self.accounts_workbook_to_cell(workbook);
                if(self.write_db) {
                    self.add_to_db(self.account_table, user_accounts);
                }
                self.account_workbook = workbook;

            }).then( function() {

                self.download_xls(self.voucher_store_url).then( (workbook) => {

                    // loading
                    console.log('Processing workbook...');
                    var process_workbook;
                    (process_workbook = ( (workbook) => {
                        
                        // get bjects from workbook
                        var voucher_objects = self.voucher_workbook_to_cell(workbook);
                        console.log("Data loaded in", new Date());
                        
                        // add new data
                        if(self.write_db) {
                            self.add_to_db(self.voucher_table, voucher_objects);
                            console.log("Data added in", new Date());
                        }

                        self.voucher_workbook = workbook;

                        console.log('Audit system is ready.');

                        // finally do this.
                        then(self);

                    }))(workbook);
                });
            });

        });
    }
};
